package com.eascs.web.o2o.report.service;

import java.io.File;
import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashSet;
import java.util.List;

import javax.inject.Inject;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import com.alibaba.dubbo.config.annotation.Reference;
import com.eascs.app.exception.ControllerException;
import com.eascs.app.log.Logger;
import com.eascs.app.log.LoggerFactory;
import com.eascs.app.runtime.RuntimeSetting;
import com.eascs.common.doc.excel.ExcelApiHelper;
import com.eascs.common.util.IoUtil;
import com.eascs.common.util.UuidUtil;
import com.eascs.notify.constant.ModelResult;
import com.eascs.notify.email.EmailSender;
import com.eascs.notify.email.entity.EmailContentEntity;
import com.eascs.notify.email.entity.EmailSourceSetting;
import com.eascs.notify.mail.MailNotifier;
import com.eascs.notify.mail.MailNotifyRequest;
import com.eascs.notify.mail.MailNotifyResponse;
import com.eascs.notify.mail.MailSendType;
import com.eascs.web.o2o.report.constants.FefundConstants;
import com.eascs.web.o2o.report.dao.WorkflowReportDao;
import com.eascs.web.o2o.report.util.BankRowWriteHandler;
import com.eascs.web.o2o.report.util.DateUtil;
import com.eascs.web.o2o.scheduler.dao.BaseDao;
import com.eascs.workflow.common.service.sys.SysPermissionService;

@Service
public class ProcessMonitorReportService {

	protected final Logger logger = LoggerFactory.getLogger(getClass());
	@Inject
	BaseReportService baseReportService;
	@Inject
	BaseDao baseDao;
	@Inject
	WorkflowReportDao workflowReportDao;
	@Reference
	SysPermissionService sysPermissionService;
	@Autowired
	private EmailSender emailSender;
	@Autowired
	EmailSourceSetting emailSourceSetting;
	
	@Value("${email.toemail.dev}")
	String devmail;
	
	@Value("${email.toemailprocess}")
	String processmail;
	
	@Value("${email.toemail.refund}")
	String refundmail;

	@Reference
	MailNotifier mailNotifier;
	

	

	//管理层日报二期-省区流程监控表
	public String  processMonitorReport() {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String date = sdf.format(new Date());
		String yesterday=DateUtil.getSpecifiedDayBefore(1);//昨天时间
		String regionalSql = " SELECT pmr.regional_name FROM process_monitor_report pmr GROUP BY pmr.regional_name ORDER BY CONVERT (pmr.regional_name USING gbk) ASC ";
		List<Object[]> list = baseDao.findObjectsList(regionalSql);
		for (int i = 0; i < list.size(); i++) {
			Object[] b = list.get(i);
			String sql = "SELECT (@rowNO := @rowNo + 1) AS rowno, p.* FROM ( SELECT pmr.ltd_name, pmr.operator_name, pmr.user_full_name, ( CASE WHEN pmr.loan_type = 'BP' THEN '品牌贷' WHEN pmr.loan_type = 'FQ' THEN '分期贷' WHEN pmr.loan_type = 'EA' THEN '接力贷' ELSE '终端贷' END ) AS loan_type, ( CASE WHEN pmr.wf_type = 'NEW' THEN '新建' WHEN pmr.wf_type = 'Z' THEN '征信中' WHEN pmr.wf_type = 'ZF' THEN '征信完成' WHEN pmr.wf_type = 'N' THEN '内审中' WHEN pmr.wf_type = 'NF' THEN '内审完成' ELSE '申报中' END ) AS wf_type, pmr.create_date, pmr.loana_mount, pmr.ea_mount FROM process_monitor_report pmr WHERE pmr.regional_name = '" + b[0] + "' ORDER BY pmr.ltd_name, pmr.operator_name, pmr.create_date ) p, (SELECT @rowNO := 0) b";
			
			String soucreFileName = "/excelTemplate/excel-process-monitor-report.xls";
			String filename = "省区流程监控表-" + b[0] + yesterday + ".xls";
			String filePath = emailSourceSetting.getFilepath()+"/"+ b[0] + date + "/" + filename;
			logger.info("生成文件路径为：[{}]", filePath);
			
			int[] arr = { 7, 8 };
			
			String title = "省区流程监控表" + yesterday;	
			int sheetIndex=0;
			int rowIndex=2;
			int columnIndex=0;
			write(sql, soucreFileName, filePath, 9,arr,Arrays.asList(title),sheetIndex,rowIndex,columnIndex);
		}
		
		return emailSourceSetting.getFilepath()+"/"+date+ "/processmonitor/";
	}
	
	

	/*
	 * 发送邮件--管理层日报二期-省区流程监控表
	 */
	public void process_sendMail() {
		logger.info("=============开始发送邮件（省区流程监控表）--日邮件===============");
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String date = sdf.format(new Date());
		String regionalSql = " SELECT pmr.regional_name FROM process_monitor_report pmr GROUP BY pmr.regional_name ORDER BY CONVERT (pmr.regional_name USING gbk) ASC ";
		String emailSql = "select email from province_email where province = ?";
		List<Object[]> list = baseDao.findObjectsList(regionalSql);
		for( int i = 0; i < list.size(); i++ ){
			Object[] b = list.get(i);
			String email = baseDao.findStringDev(emailSql, b[0]);
			try{
				ArrayList<String> listDirectories=new ArrayList<String>();
				if(listDirectories==null||listDirectories.size()==0){
					IoUtil.createDirectorieIfMissing(new File(emailSourceSetting.getFilepath()+"/"+ b[0] + date).getParentFile());
					listDirectories.add(emailSourceSetting.getFilepath()+"/"+ b[0] + date); 
				}
				/*ModelResult<List<User>> result=sysPermissionService.getUserWithUnitNoAndRoleCode(unitNo, roleCodeList);
				if(result.isSuccess() && result.getModel()!=null){
					
				}*/
				EmailContentEntity entity = new EmailContentEntity();
				entity.setHost(emailSourceSetting.getHost());
				entity.setUsername(emailSourceSetting.getFrom());
				entity.setPassword(emailSourceSetting.getPassword());
				entity.setFrom(emailSourceSetting.getFrom());
				entity.setSubject("省区流程监控表" + b[0] + date);
				entity.setContent("省区流程监控表" + b[0] + date);
				
				//String[] receivers = emailSourceSetting.getToProcess().split(",");
				
				//list去重
				HashSet<String> h = new  HashSet<String>(listDirectories); 
				listDirectories.clear(); 
				listDirectories.addAll(h); 
				entity.setAttachmentDirectories(listDirectories);
				RuntimeSetting runtimeSetting = RuntimeSetting.get();
				if(runtimeSetting.getEnv().equals(FefundConstants.ENV_PROD)){
					entity.setTo(email);
				}else{
					entity.setTo("yuanhui.gao@eascs.com");
				}
				logger.info("邮件发送成功，文件路径：[{}]，收件人：[{}]，省区：[{}]",listDirectories,email,b[0]);
				emailSender.send(entity);
				//短信入库
				MailNotifyRequest mailNotifyRequest = new MailNotifyRequest();
				mailNotifyRequest.setTitle("省区流程监控表" + date);
				mailNotifyRequest.setContent("省区流程监控表" + date);
				mailNotifyRequest.setSystemCode("报表系统");
				mailNotifyRequest.setMailSendType(MailSendType.INSTANT);
				mailNotifyRequest.setRequestId(UuidUtil.generateShortUuid());
				mailNotifyRequest.setRequestDateTime(new Date());
				mailNotifyRequest.setBizCode("省区流程监控表");
				mailNotifyRequest.setRecipientTo(email);
				mailNotifyRequest.setAttachmentPath(emailSourceSetting.getFilepath()+"/"+ b[0] + date);
				ModelResult<MailNotifyResponse> model = null;
				try{
					model = mailNotifier.notify(mailNotifyRequest);
					if (model.isSuccess()) {
					workflowReportDao.excuteSql("update wf_email set status = 1 where request_id = ?", model.getModel().getResponseId());
					logger.info("入库成功-文件路径：[{}]，收件人：[{}]，省区：[{}]",listDirectories,email,b[0]);
					}
				}catch(Exception e){
					logger.info("入库异常-文件路径：[{}]，收件人：[{}]，省区：[{}]",listDirectories,email,b[0]);
					e.printStackTrace();
				}
				}catch(Exception e){
					logger.info("省区[{}]发送异常:[{}]",b[0],e.getMessage());
				}
			}
		logger.info("=============结束发送邮件（省区流程监控表）--日邮件===============");
	}
	
	
	
	
	
	/*
	 * 生成excel
	 * 参数：数据，源文件，输出文件，excel包含的列，需要统计的列，excel标题
	 */
	public void write(String sql, String soucreFileName, String outPath, int col,int[] arr,List<String> title,int sheetIndex,int rowIndex,int columnIndex) {
		List<Object[]> data = baseReportService.findObjectsList(sql);
		IoUtil.createDirectorieIfMissing(new File(outPath).getParentFile());
		try {
			ExcelApiHelper.writeHandle(new FileOutputStream(outPath), ProcessMonitorReportService.class.getResourceAsStream(soucreFileName), new BankRowWriteHandler(data, col,arr,title,sheetIndex,rowIndex,columnIndex));
			
		} catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new ControllerException(e);
		}
	}
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
}
